﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class SelfEvent : BasicSelfService, ISelfEvent
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
        private const string sqlInsert = "insert into servicelist (servicename,serviceid,needapprove,cp,isenent,applvl1,applvl2,applvl3,partialname) values(:servicename,:serviceid,:needapprove,:cp,:isenent,:applvl1,:applvl2,:applvl3,:partialname)";
        private const string sqlDelete = "delete from servicelist where id=:id";
        private const string sqlGetEventbyName = "select * from servicelist where ServiceName=:name";
        private const string sqlupdate = "update servicelist set servicename=:Name where ID=:id";
        private const string sqlgetallevent = "select * from servicelist where servicename!='应用系统' and servicename!='IT故障申报' and servicename!='VPN' and servicename!='内部请求' and servicename!='业务需求' and servicename!='安全防病毒'";
        private const string sqleventCategory = "select * from servicelist where serviceid is null and servicename!='应用系统' and servicename!='IT故障申报' and servicename!='VPN' and servicename!='内部请求' and servicename!='业务需求' and servicename!='安全防病毒'";
        

        public List<SelfEventModel> GetAllEvent() 
        {
            return ModelHelper<SelfEventModel>.GetModel_By_DirectSql(sqlgetallevent, null, oda);
        }

        public List<SelfEventModel> GetCategory() 
        {
            return ModelHelper<SelfEventModel>.GetModel_By_DirectSql(sqleventCategory, null, oda);
        }

        public SelfEventModel GetSelfEventByName(string name)
        {
            SelfEventModel model = new SelfEventModel();

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from servicelist where ServiceName=:name";
                var name1 = new OracleParameter("ServiceName", OracleDbType.NVarchar2, 100);
                name1.Value = name;

                com.Parameters.Add(name1);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ID"] != DBNull.Value) model.ID = (decimal)reader["ID"];
                        if (reader["ServiceName"] != DBNull.Value) model.ServiceName = (string)reader["ServiceName"];
                        if (reader["ServiceID"] != DBNull.Value) model.ServiceID = (decimal)reader["ServiceID"];
                        if (reader["NeedApprove"] != DBNull.Value) model.NeedApprove = (int)reader["NeedApprove"];
                        if (reader["CP"] != DBNull.Value) model.CP = (int)reader["CP"];
                        if (reader["IsEnent"] != DBNull.Value) model.IsEnent = (int)reader["IsEnent"];
                        if (reader["Applvl1"] != DBNull.Value) model.Applvl1 = (string)reader["Applvl1"];
                        if (reader["Applvl2"] != DBNull.Value) model.Applvl2 = (string)reader["Applvl2"];
                        if (reader["Applvl3"] != DBNull.Value) model.Applvl3 = (string)reader["Applvl3"];
                        if (reader["PartialName"] != DBNull.Value) model.PartialName = (string)reader["PartialName"];
                    }
                }
            }
            return model;
        }

        public SelfEventModel GetSelfEventByID(int id)
        {
            SelfEventModel model = new SelfEventModel();

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from servicelist where id=:id";
                var id1 = new OracleParameter("id", OracleDbType.Decimal, 100);
                id1.Value = id;

                com.Parameters.Add(id1);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ID"] != DBNull.Value) model.ID = (decimal)reader["ID"];
                        if (reader["ServiceName"] != DBNull.Value) model.ServiceName = (string)reader["ServiceName"];
                        if (reader["ServiceID"] != DBNull.Value) model.ServiceID = (decimal)reader["ServiceID"];
                        //if (reader["NeedApprove"] != DBNull.Value) model.NeedApprove = (int)reader["NeedApprove"];
                        //if (reader["CP"] != DBNull.Value) model.CP = (int)reader["CP"];
                        //if (reader["IsEnent"] != DBNull.Value) model.IsEnent = (int)reader["IsEnent"];
                        if (reader["Applvl1"] != DBNull.Value) model.Applvl1 = (string)reader["Applvl1"];
                        if (reader["Applvl2"] != DBNull.Value) model.Applvl2 = (string)reader["Applvl2"];
                        if (reader["Applvl3"] != DBNull.Value) model.Applvl3 = (string)reader["Applvl3"];
                        if (reader["PartialName"] != DBNull.Value) model.PartialName = (string)reader["PartialName"];
                    }
                }
            }
            return model;
        }


        public int Add(SelfEventModel model)
        {
            int result = 0;
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlInsert, new List<OracleParameter>() 
                        { 
                            new OracleParameter("ServiceName", OracleDbType.NVarchar2, 100, model.ServiceName, ParameterDirection.Input),
                            new OracleParameter("ServiceID", OracleDbType.Decimal, 100, model.ServiceID, ParameterDirection.Input),
                            new OracleParameter("NeedApprove", OracleDbType.Decimal, 100, model.NeedApprove, ParameterDirection.Input),
                            new OracleParameter("CP", OracleDbType.Decimal, 100, model.CP, ParameterDirection.Input),
                            new OracleParameter("IsEnent", OracleDbType.Decimal, 100, model.IsEnent, ParameterDirection.Input),
                            new OracleParameter("Applvl1", OracleDbType.NVarchar2, 100, model.Applvl1, ParameterDirection.Input),
                            new OracleParameter("Applvl2", OracleDbType.NVarchar2, 100, model.Applvl2, ParameterDirection.Input),
                            new OracleParameter("Applvl3", OracleDbType.NVarchar2, 100, model.Applvl3, ParameterDirection.Input),
                            new OracleParameter("PartialName", OracleDbType.NVarchar2, 100, model.PartialName, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
                result = 1;
            }
            catch (Exception e)
            {
                result = 0;
            }
            return result;
        }

        public void Delete(int ID)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlDelete, new List<OracleParameter>() 
                        { 
                            new OracleParameter("id", OracleDbType.Decimal, 100, ID, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
        }

        public int Update(SelfEventModel model)
        {
            int result = 0;
            if (model != null)
            {
                try
                {
                    using (OracleConnection conn = new OracleConnection(connstr))
                    {
                        conn.Open();
                        OracleCommand com = new OracleCommand();
                        this.PrepareCommand(com, conn, null, CommandType.Text, sqlupdate, new List<OracleParameter>() 
                        { 
                            new OracleParameter("id", OracleDbType.Decimal, 100, model.ID, ParameterDirection.Input),
                            new OracleParameter("ServiceName", OracleDbType.NVarchar2, 100, model.ServiceName, ParameterDirection.Input)
                        });
                        com.ExecuteNonQuery();
                    }
                    result = 1;
                }
                catch (Exception e)
                {
                    result = 0;
                }
            }
            return result;
        }

        public List<SelfEventModel> GetExistEvent(string serviceName)
        {
            return ModelHelper<SelfEventModel>.GetModel_By_DirectSql(sqlGetEventbyName, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="serviceName",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=serviceName}
                 },
          oda);
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

    }
}

